﻿using SupermarketSalesModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SupermarketSalesController
{
    /*
     *         void add(ProductxPromotion pxp);
        void update(ProductxPromotion pxp);
        void delete(int productId);
        List<ProductxPromotion> queryAll();
        ProductxPromotion queryById(int pxpId);
        int count();
     */
    class SQLServerDAOProductxPromotion : DAOProductxPromotion
    {
        void DAOProductxPromotion.add(ProductxPromotion p)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO G3_PRODUCTXPROMOTION(idProduct, idPromotion, startDate, endDate," +
                    "status) VALUES (@param1,@param2,@param3,@param4,@param5)";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param3 = new SqlParameter("@param3",
                                                        System.Data.SqlDbType.Date);
                SqlParameter param4 = new SqlParameter("@param4",
                                                        System.Data.SqlDbType.Date);
                SqlParameter param5 = new SqlParameter("@param5",
                                                        System.Data.SqlDbType.Int);

                
                param1.Value = p.Producto.Id;
                param2.Value = p.Promotion.Id;
                param3.Value = p.FechaInicio;
                param4.Value = p.FechaFin;
                param5.Value = p.Estado;

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);
                comm.Parameters.Add(param5);


                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }

        }


        void DAOProductxPromotion.update(ProductxPromotion p)
        { 
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                /*
                 *                  comm.CommandText = "INSERT INTO G3_PRODUCTXPROMOTION(idProduct, idPromotion, startDate, endDate," +
                              "status) VALUES (@param1,@param2,@param3,@param4,@param5)";
                 * */
                comm.CommandText = "UPDATE G3_PRODUCTXPROMOTION SET idProduct=@param1, idPromotion=@param2," +
                        "startDate=@param3, endDate=@param4, status=@param5 WHERE idPxP=@param6";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param3 = new SqlParameter("@param3",
                                                        System.Data.SqlDbType.Date);
                SqlParameter param4 = new SqlParameter("@param4",
                                                        System.Data.SqlDbType.Date);
                SqlParameter param5 = new SqlParameter("@param5",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param6 = new SqlParameter("@param6",
                                                        System.Data.SqlDbType.Int);


                param1.Value = p.Producto.Id;
                param2.Value = p.Promotion.Id;
                param3.Value = p.FechaInicio;
                param4.Value = p.FechaFin;
                param5.Value = p.Estado;
                param6.Value = p.Id;

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);
                comm.Parameters.Add(param5);
                comm.Parameters.Add(param6);


                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }     
        }

        void DAOProductxPromotion.delete(int pxpId)
        {        /*
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "DELETE FROM G3_PROMOTION " +
                                            " WHERE idPromotion=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                param1.Value = pxpId;

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }   */
        }

        List<ProductxPromotion> DAOProductxPromotion.queryAll()
        {
            List<ProductxPromotion> list = new List<ProductxPromotion>();
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM G3_PRODUCTXPROMOTION";
                /*
                 *   comm.CommandText = "INSERT INTO G3_PRODUCTXPROMOTION(idProduct, idPromotion, startDate, endDate," +
                     "status) VALUES (@param1,@param2,@param3,@param4,@param5)";
                 * */

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();
              
                //Paso 5: Evaluar los resultados
                while (myReader.Read())
                {
                    ProductxPromotion p = new ProductxPromotion();
                    p.Id = Int32.Parse(myReader["idPxP"].ToString());
                    int idProduct= Int32.Parse(myReader["idProduct"].ToString());
                    p.Producto = SalesManager.getProductbyId(idProduct);
                    int idPromotion = Int32.Parse(myReader["idPromotion"].ToString());
                    p.Promotion = SalesManager.getPromotionById(idPromotion);
                    p.FechaInicio = DateTime.Parse(myReader["startDate"].ToString());
                    p.FechaFin = DateTime.Parse(myReader["endDate"].ToString());
                    p.Estado = Int32.Parse(myReader["status"].ToString());
                    list.Add(p);
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 6. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return list;
               
            
        }

        ProductxPromotion DAOProductxPromotion.queryById(int pxpId)
        {   
            ProductxPromotion p = null;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM G3_PRODUCTXPROMOTION WHERE idPxP=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                param1.Value = pxpId;
                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {

                    p = new ProductxPromotion();
                    p.Id = Int32.Parse(myReader["idPxP"].ToString());
                    int idProduct = Int32.Parse(myReader["idProduct"].ToString());
                    p.Producto = SalesManager.getProductbyId(idProduct);
                    int idPromotion = Int32.Parse(myReader["idPromotion"].ToString());
                    p.Promotion = SalesManager.getPromotionById(idPromotion);
                    p.FechaInicio = DateTime.Parse(myReader["startDate"].ToString());
                    p.FechaFin = DateTime.Parse(myReader["endDate"].ToString());
                    p.Estado = Int32.Parse(myReader["status"].ToString());
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return p;  
           
        }

        int DAOProductxPromotion.count()
        {    
            int quantity = 0;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Count(1) AS quantity FROM G3_PRODUCTXPROMOTION";
                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {
                    quantity = Int32.Parse(myReader["quantity"].ToString());
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return quantity;
                
            
        }
             
    }
}
